﻿using ConfExpert.Datasets;
using ConfExpert.Utility;
using DevExpress.XtraEditors;
using DevTeam;


using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace ConfExpert.Forms.ImportWizards
{
    public partial class CafeCotton_Tip2 : XImportWizard
    {
        public CafeCotton_Tip2()
        {
            InitializeComponent();

            Init("Cafe Cotton Ara Sezon Sipariş Dosyası", false);

            this.FileDone += CafeCotton_Tip2_FileDone;
            this.ProcessDone += CafeCotton_Tip2_ProcessDone;
            this.ProcessStart += CafeCotton_Tip2_ProcessStart;
            this.btnSave.ItemClick += BtnSave_ItemClick;
        }

        DataTable dtResult;
        void CafeCotton_Tip2_ProcessStart()
        {
            dtResult = new DataTable();
            dtResult.Columns.Add("ProductId", typeof(int));
            dtResult.Columns.Add("SezonId", typeof(int));

            DataSet dsResult = new DataSet();
            dsResult.Tables.Add(dtResult);

            dtResult.ColumnChanged -= dtResult_ColumnChanged;
        }

        void CafeCotton_Tip2_ProcessDone()
        {
            var distinctValues = dtResult.AsEnumerable()
                      .Select(row => new
                      {
                          Kod = row.Field<string>("Article"),
                      })
                      .Distinct().ToList();

            string codes = string.Join(",", Array.ConvertAll(distinctValues.ToArray(), i => i.Kod));


            DataTable dtId = Session.DataManager.ExecuteDataTable("GetProductIdByCode", codes);
            foreach (DataRow drParsed in dtResult.Rows)
            {
                DataRow[] drIds = dtId.Select("Kod='" + drParsed["Article"] + "'");
                if (drIds.Length == 1)
                    drParsed["ProductId"] = (int)drIds[0]["ProductId"];
            }

            dtResult = dtResult.Select("isnull(TotalQty,0) > 0").CopyToDataTable();


            MasterGrid.Init(dtResult);

            MasterGrid.AddSummary();
            MasterGrid.AddSummary("TotalQty");

            MasterGrid.MakeReadOnly("ProductId", "SezonId");
            MasterGrid.BestFitColumns();

            dtResult.ColumnChanged += dtResult_ColumnChanged;

            btnSave.Enabled = true;
        }


        void CafeCotton_Tip2_FileDone(DataSet ds, string FileName)
        {
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                if (!"synthese;SYNTHESIS;QTYBYCLIENT;MATRICE;KONTROL".ToLower().Contains(dt.TableName.Replace(" ", "").ToLower()))
                {
                    DataTable dtSheet = ProcessSheet(dt);

                    dtSheet.Columns.Add("SheetName", typeof(string));
                    dtSheet.Columns.Add("FileName", typeof(string));

                    foreach (DataRow dr in dtSheet.Rows)
                    {
                        dr["SheetName"] = dt.TableName;
                        dr["FileName"] = FileName;  //*
                    }

                    dtResult.Merge(dtSheet);
                }
            }
        }

        void dtResult_ColumnChanged(object sender, DataColumnChangeEventArgs e)
        {
            if (e.Column == dtResult.Columns["ProductId"])
            {
                dtResult.ColumnChanged -= dtResult_ColumnChanged;
                int productId = (int)e.Row["ProductId"];
                string artikel = (string)e.Row["Article"];
                foreach (DataRow dr in dtResult.Rows)
                {
                    if ((string)dr["Article"] == artikel)
                    {
                        dr["ProductId"] = productId;
                    }
                }
                dtResult.ColumnChanged += dtResult_ColumnChanged;
            }
            else if (e.Column == dtResult.Columns["SezonId"])
            {
                dtResult.ColumnChanged -= dtResult_ColumnChanged;
                int sezonId = (int)e.Row["SezonId"];
                foreach (DataRow dr in dtResult.Rows)
                {
                    dr["SezonId"] = sezonId;
                }
                dtResult.ColumnChanged += dtResult_ColumnChanged;
            }
        }




        private static DataTable ProcessSheet(DataTable dt)
        {
            //decimal sumRollQty = (decimal)dtParsed.Compute("sum(qty)", "");
            //if (!total.Equals(sumRollQty))
            //    ShowErrorMessage("Dosyadaki toplam metre ile topların metrelerinin toplamı farklı. Kontrol edin !!!");

            DataTable dtData = new DataTable();

            DataTable dtDagilim = new DataTable();
            dtDagilim.Columns.Add("Model", typeof(string));
            dtDagilim.Columns.Add("x37", typeof(int));
            dtDagilim.Columns.Add("x38", typeof(int));
            dtDagilim.Columns.Add("x39", typeof(int));
            dtDagilim.Columns.Add("x40", typeof(int));
            dtDagilim.Columns.Add("x41", typeof(int));
            dtDagilim.Columns.Add("x42", typeof(int));
            dtDagilim.Columns.Add("x43", typeof(int));
            dtDagilim.Columns.Add("x44", typeof(int));
            dtDagilim.Columns.Add("x45", typeof(int));
            dtDagilim.Columns.Add("x46", typeof(int));
            dtDagilim.Columns.Add("x47", typeof(int));
            dtDagilim.Columns.Add("x48", typeof(int));
            dtDagilim.Columns.Add("S", typeof(int));
            dtDagilim.Columns.Add("M", typeof(int));
            dtDagilim.Columns.Add("L", typeof(int));
            dtDagilim.Columns.Add("XL", typeof(int));
            dtDagilim.Columns.Add("XXL", typeof(int));
            dtDagilim.Columns.Add("XXXL", typeof(int));
            dtDagilim.Columns.Add("Total", typeof(int), "isnull(x37,0)+isnull(x38,0)+isnull(x39,0)+isnull(x40,0)+isnull(x41,0)+isnull(x42,0)+isnull(x43,0)+isnull(x44,0)+isnull(x45,0)+isnull(x46,0)+isnull(x47,0)+isnull(x48,0)+isnull(S,0)+isnull(M,0)+isnull(L,0)+isnull(XL,0)+isnull(XXL,0)+isnull(XXXL,0)");

            int ix = 0;
            try
            {
                //bool isFirstColumnEmpty = true;
                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    DataRow dr = dt.Rows[i];
                //    string firstCell = dr[0].ToStr();
                //    if (firstCell != "")
                //    {
                //        isFirstColumnEmpty = false;
                //        break;
                //    }
                //}
                //if (isFirstColumnEmpty)
                //    dt.Columns.RemoveAt(0);

                List<string> labels = new List<string>();

                int firstCellIndex = -1;
                int DagilimStart = -1;
                int DagilimEnd = -1;
                int columnCount = -1;
                string magaza = "";
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    string firstCell = dr[0].ToString();
                    string secondCell = dr[1].ToString();

                    if (secondCell == "DELIVERY ADRESS  TO NOTE ON THE SHIPING MARK AND PACKING LIST")
                    {
                        magaza = dt.Rows[i + 1][1].ToString();
                    }
                    else if (firstCell.ToUpper() == "RATIO" || firstCell.ToUpper() == "RATİO")
                    {
                        DagilimStart = i + 1;

                    }
                    else if (firstCell.ToUpper() == "RATIO END" || firstCell.ToUpper() == "RATİO END")
                    {
                        DagilimEnd = i;
                    }
                }


                if (DagilimStart == -1 || DagilimEnd == -1)
                {
                    throw new ApplicationException("RATIO/RATIO END satırları bulunamadı!");
                }


                for (int i = 0; i < DagilimStart; i++)
                {
                    DataRow dr = dt.Rows[i];
                    string firstCell = dr[0].ToString();

                    if (firstCell.ToUpper() == "RÉFÉRENCES" || firstCell == "REFERENCES" || firstCell.ToUpper() == "RÉFÉRENCE" || firstCell == "REFERENCE")
                    {
                        firstCellIndex = i + 2;
                        for (int j = 2; j < dt.Columns.Count; j++)
                        {
                            string label = dr[j].ToString();
                            string control = label.ToUpper().Trim();
                            if (control == "QTÉS" || control == "TOTAL QTÉS" || control == "COLORİS" || control == "CONSTRUCTİON")
                            {
                                continue;
                            }
                            if (label != "" && control != "TOTAL")
                            {
                                labels.Add(label);
                            }
                            else
                            {
                                columnCount = j;
                                break;
                            }
                        }
                        break;
                    }
                }

                int ratioLastColIndex = 0;
                for (int cIndex = 0; cIndex < dt.Columns.Count; cIndex++)
                {
                    if (dt.Rows[DagilimStart - 1][cIndex].ToStr() == "")
                    {
                        ratioLastColIndex = cIndex;
                        break;
                    }

                }


                if (DagilimStart != -1 && DagilimEnd != -1)
                {
                    DataRow captions = dt.Rows[DagilimStart - 1];

                    for (int i = DagilimStart; i < DagilimEnd; i++)
                    {
                        DataRow dr = dt.Rows[i];


                        DataRow drDagilim = dtDagilim.NewRow();
                        drDagilim[0] = dr[0].ToStr().Replace(" ", "").ToUpper().Replace("İ", "I");

                        for (int j = 1; j < ratioLastColIndex; j++)
                        {
                            string fieldName = captions[j].ToStr();
                            if (Char.IsNumber(fieldName, 0))
                                fieldName = "x" + fieldName;
                            if (dr[j].ToStr() != "")
                                drDagilim[fieldName] = dr[j];
                        }
                        dtDagilim.Rows.Add(drDagilim);
                    }
                }


                dtData.Columns.Add("Article", typeof(string));
                dtData.Columns.Add("Model", typeof(string));
                dtData.Columns.Add("Magaza", typeof(string));
                //dtData.Columns.Add("Construction", typeof(string));
                //dtData.Columns.Add("Description", typeof(string));
                dtData.Columns.Add("TotalQty", typeof(int));
                dtData.Columns.Add("DagilimId", typeof(int));

                dtData.Columns.Add("x37", typeof(int));
                dtData.Columns.Add("x38", typeof(int));
                dtData.Columns.Add("x39", typeof(int));
                dtData.Columns.Add("x40", typeof(int));
                dtData.Columns.Add("x41", typeof(int));
                dtData.Columns.Add("x42", typeof(int));
                dtData.Columns.Add("x43", typeof(int));
                dtData.Columns.Add("x44", typeof(int));
                dtData.Columns.Add("x45", typeof(int));
                dtData.Columns.Add("x46", typeof(int));
                dtData.Columns.Add("x47", typeof(int));
                dtData.Columns.Add("x48", typeof(int));
                dtData.Columns.Add("S", typeof(int));
                dtData.Columns.Add("M", typeof(int));
                dtData.Columns.Add("L", typeof(int));
                dtData.Columns.Add("XL", typeof(int));
                dtData.Columns.Add("XXL", typeof(int));
                dtData.Columns.Add("XXXL", typeof(int));

                string caption = "";
                bool skip = false;

                for (ix = firstCellIndex; ix < DagilimStart - 1; ix++)
                {
                    DataRow dr = dt.Rows[ix];
                    string firstCell = dr[0].ToString();

                    List<string> rowValues = new List<string>();
                    for (int j = 0; j < columnCount; j++)
                    {
                        string val = dr[j].ToString();
                        rowValues.Add(val);
                    }

                    bool allEmpty = true;
                    foreach (string v in rowValues)
                    {
                        if (v != "")
                        {
                            allEmpty = false;
                            break;
                        }
                    }
                    if (allEmpty)
                    {
                        continue;
                    }
                    else
                    {
                        if (rowValues[0] != "")
                        {
                            caption = rowValues[0].Trim();
                            if (caption.ToLower() == "total" || caption.ToLower() == "grand total" || caption.ToLower() == "total 1" || caption.ToLower() == "total 2")
                            {
                                skip = true;
                                continue;
                            }
                            else if (caption == "RÉFÉRENCES" || caption == "REFERENCES")
                            {
                                skip = false;
                                continue;
                            }
                        } if (!skip)
                        {
                            if (rowValues[1] != "")
                            {
                                rowValues[0] = caption + "/" + rowValues[1].PadLeft(2, '0');
                                rowValues[1] = "";
                            }

                            for (int k = 2; k < rowValues.Count; k++)
                            {
                                DataRow drNew = dtData.NewRow();
                                string article = rowValues[0].ToUpper().Replace("İ", "I");
                                drNew[0] = article;
                                //drNew["Description"] = rowValues[2].Trim();
                                string model = labels[k - 2].Replace(" ", "").ToUpper().Replace("İ", "I");
                                drNew["Model"] = model;
                                drNew["Magaza"] = magaza;
                                int baseQty = rowValues[k].Replace(",", "").Replace(".", "").ToInt(-1);
                                if (baseQty == -1) baseQty = 0;
                                drNew["TotalQty"] = baseQty;
                                //TODO : drNew["Construction"] = rowValues[3].Trim();
                                //drNew["Construction"] = "";


                                if (model == "BOXER" || model == "BOXERS")
                                {
                                    drNew["Model"] = "BOXER";
                                    model = "99CAL";
                                }

                                DataRow[] drsDagilim = dtDagilim.Select("Model='" + model + "'");

                                if (drsDagilim.Length == 1)
                                {
                                    DataRow drCarpan = drsDagilim[0];
                                    int total = drCarpan["Total"].ToInt(-1);
                                    if (total > 0)
                                    {
                                        int carpan = baseQty / total;

                                        drNew["x37"] = GetCount(drCarpan["x37"], carpan);
                                        drNew["x38"] = GetCount(drCarpan["x38"], carpan);
                                        drNew["x39"] = GetCount(drCarpan["x39"], carpan);
                                        drNew["x40"] = GetCount(drCarpan["x40"], carpan);
                                        drNew["x41"] = GetCount(drCarpan["x41"], carpan);
                                        drNew["x42"] = GetCount(drCarpan["x42"], carpan);
                                        drNew["x43"] = GetCount(drCarpan["x43"], carpan);
                                        drNew["x44"] = GetCount(drCarpan["x44"], carpan);
                                        drNew["x45"] = GetCount(drCarpan["x45"], carpan);
                                        drNew["x46"] = GetCount(drCarpan["x46"], carpan);
                                        drNew["x47"] = GetCount(drCarpan["x47"], carpan);
                                        drNew["x48"] = GetCount(drCarpan["x48"], carpan);
                                        drNew["S"] = GetCount(drCarpan["S"], carpan);
                                        drNew["M"] = GetCount(drCarpan["M"], carpan);
                                        drNew["L"] = GetCount(drCarpan["L"], carpan);
                                        drNew["XL"] = GetCount(drCarpan["XL"], carpan);
                                        drNew["XXL"] = GetCount(drCarpan["XXL"], carpan);
                                        drNew["XXXL"] = GetCount(drCarpan["XXXL"], carpan);


                                        int sumN = GetSum(drNew, "x37", "x38", "x39", "x40", "x41", "x42", "x43", "x44", "x45", "x46", "x47", "x48");

                                        drNew["S"] = GetCount(drCarpan["S"], carpan);
                                        drNew["M"] = GetCount(drCarpan["M"], carpan);
                                        drNew["L"] = GetCount(drCarpan["L"], carpan);
                                        drNew["XL"] = GetCount(drCarpan["XL"], carpan);
                                        drNew["XXL"] = GetCount(drCarpan["XXL"], carpan);
                                        drNew["XXXL"] = GetCount(drCarpan["XXXL"], carpan);

                                        int sumX = GetSum(drNew, "S", "M", "L", "XL", "XXL", "XXXL");

                                        if (sumN == 0 && sumX > 0)
                                            drNew["DagilimId"] = 10;
                                        else if (sumN > 0 && sumX == 0)
                                            drNew["DagilimId"] = 7;
                                        else if (sumN == 0 && sumX == 0)
                                            drNew["DagilimId"] = DBNull.Value;
                                        else
                                            throw new ApplicationException("Her iki beden dağılımından değer var !!!");

                                    }

                                    dtData.Rows.Add(drNew);
                                }
                            }
                        }
                    }

                }
                return dtData;
            }
            catch (ApplicationException aex)
            {
                MessageBox.Show(aex.Message);
                return dt;
            }
            catch (Exception ex)
            {
                throw new ApplicationException(dt.TableName + " ex:" + ex.ToString(), ex);

            }

        }

        private static int GetSum(DataRow dr, params string[] colNames)
        {
            int sum = 0;
            foreach (string colName in colNames)
            {
                if (dr[colName] == null || dr[colName] == DBNull.Value)
                { }
                else
                {
                    sum = (int)dr[colName];
                }
            }
            return sum;
        }

        public static object GetCount(object DBCarpan, int carpan)
        {
            if (DBCarpan == DBNull.Value)
                return DBNull.Value;
            else if (DBCarpan.ToInt() == 0)
                return DBNull.Value;
            else if (DBCarpan.ToInt() * carpan == 0)
                return DBNull.Value;
            else
                return DBCarpan.ToInt() * carpan;
        }

        private void BtnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DataRow[] drs = dtResult.Select("SezonId is null or ProductId is null");
            if (drs.Length > 0)
            {
                DialogResult dr = XtraMessageBox.Show("Sezonu ya da Product'ı set edilmemiş satırlar var. Bu ekranda, düzeltmeleri yapmak çok daha kolay. Düzeltmek ister misiniz?", "Uyarı", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
                if (dr == System.Windows.Forms.DialogResult.No)
                    Kaydet();
            }
            else
            {
                Kaydet();
            }
        }

        private void Kaydet()
        {
            if (dtResult.Select("DagilimId is null").Length > 0)
            {
                ShowErrorMessage("Dağılımı belli olmayan satırlar var. Dağılım sütunu boş olan satırlar için Excel dosyasını kontrol edin.");
                return;
            }

            this.Cursor = Cursors.WaitCursor;

            DataTable dtDB = dtResult.Copy();

            MyData.OrdersDataTable dtKauf = new MyData.OrdersDataTable();

            ds.DagilimC.Merge(ExecuteDataTable("GetDagilimC", 149));
            Datasets.MyData.DagilimCRow d1 = ds.DagilimC.FindByDagilimIdCustomerId(7, 149);
            Datasets.MyData.DagilimCRow d2 = ds.DagilimC.FindByDagilimIdCustomerId(10, 149);

            foreach (DataRow dr in dtDB.Rows)
            {
                MyData.OrdersRow ordersRow = dtKauf.NewOrdersRow();

                ordersRow.DosyaAdi = (string)dr["FileName"];

                int dagilimId = (int)dr["DagilimId"];
                ordersRow.DagilimId = dagilimId;
                ordersRow.BrandId = 339;

                if (dr["SezonId"] != DBNull.Value)
                    ordersRow.SezonId = (int)dr["SezonId"];

                ordersRow.Ek45 = (string)dr["Magaza"]; // Mağaza ülkesi alanını kullanıyoruz
                dtKauf.Ek45Column.ColumnName = "MagazaDagilimi";
                ordersRow.Model = dr["Model"].ToString();

                if (dr["ProductId"] != DBNull.Value)
                    ordersRow.ProductId = (int)dr["ProductId"];
                if (dr["SezonId"] != DBNull.Value)
                    ordersRow.Sezon = AppSession.Lookups.Sezon.FindBySezonId((int)dr["SezonId"]).Name;


                ordersRow.Artikel = (string)dr["Article"];
                //ordersRow.TotalPerPlan = (int)dr["TotalQty"];
                //kaufRow.Karisim = (string)dr["Construction"];
                //kaufRow.Notlar = (string)dr["Description"];

                DataRow drTemplate = null;
                if (dagilimId == 7)
                {
                    drTemplate = d1;
                }
                else if (dagilimId == 10)
                {
                    drTemplate = d2;
                }

                for (int i = 1; i <= 28; i++)
                {
                    string colName = String.Format("A{0:00}", i);
                    string colName2 = String.Format("A{0:00}", i);
                    object orjName = drTemplate[colName2];
                    if (orjName != DBNull.Value)
                    {
                        ordersRow[colName] = dr[orjName.ToString()];

                    }
                }

                dtKauf.Rows.Add(ordersRow);
            }

            ExecuteNonQuery("InsCafeCottonFlashOrder", dtKauf.SerializeChanges());
            this.Cursor = Cursors.Default;
            ShowInfoMessage("Siparişler başarıyla aktarıldı.");

        }


    }
}